USE HierarchyIdDemo
GO

-- Reporting an SelfJoin.Org chart with a parent child
-- schema (e.g., a ParentDepartmentID field in
-- the HierarchyId.Org table) would require recursion.  But
-- with the HierarchyID, this can be done with
-- one SELECT statement.
DROP PROCEDURE HierarchyId.ShowDepartmentChartAndParent
GO

CREATE PROCEDURE HierarchyId.ShowDepartmentChartAndParent
	@DepartmentId INT
AS
BEGIN

	DECLARE @TopNode hierarchyid,
		@TopLevel INT

	SELECT @TopNode = Node,
			@TopLevel = Level
		FROM HierarchyId.Org
		WHERE DepartmentId = @DepartmentId

	-- note that parent is descENDent of itself
	SELECT Child.Node.ToString() NodeText, 
			Parents.DepartmentId ParentId,
			Child.DepartmentId DepartmentId,
			Child.DepartmentName Department
		FROM HierarchyId.Org Parents
				JOIN HierarchyId.Org Child
					ON Child.Node.GetAncestor(1) = Parents.Node
		WHERE @TopNode.IsDescendant(Child.Node) = 1
		ORDER BY Child.Node
	
END

GO

EXEC HierarchyId.ShowDepartmentChartAndParent 2
EXEC HierarchyId.ShowDepartmentChartAndParent 1

GO
